create or replace function fn_scanfolder (
	int_userid int,
	int_scanfolderid int,
	varchar_path varchar,
	int_filetypeid int,
	varchar_namepattern varchar,
	varchar_dateregex varchar,
	boolean_active boolean,
	int_duplicate int,
	varchar_tag1 varchar,
	varchar_tag2 varchar,
	varchar_tag3 varchar,
	varchar_tag4 varchar,
	varchar_tag5 varchar
	) returns integer as
$function$

DECLARE retval int;

-- Create or update a scanfolder record.
BEGIN
        IF fn_hasrole(int_userid, 'Admin'::varchar) THEN

		IF int_scanfolderid IS NULL THEN

			-- Insert
			INSERT INTO scanfolder (
				createdbyid,
				path,
				filetypeid,
				namepattern,
				dateregex,
				duplicate,
				tag1,
				tag2,
				tag3,
				tag4,
				tag5)
			VALUES (int_userid,
				varchar_path,
				int_filetypeid,
				varchar_namepattern,
				varchar_dateregex,
				int_duplicate,
				varchar_tag1,
				varchar_tag2,
				varchar_tag3,
				varchar_tag4,
				varchar_tag5)
			RETURNING scanfolderid INTO retval;

			RETURN retval;
		ELSE

			-- Update
			UPDATE scanfolder
			SET path = varchar_path,
				filetypeid = int_filetypeid,
				namepattern = varchar_namepattern,
				dateregex = varchar_dateregex,
				duplicate = int_duplicate,
				active = boolean_active,
				tag1 = varchar_tag1,
				tag2 = varchar_tag2,
				tag3 = varchar_tag3,
				tag4 = varchar_tag4,
				tag5 = varchar_tag5
			WHERE scanfolderid = int_scanfolderid;

			RETURN int_scanfolderid;

		END IF;
	END IF; 
END;

$function$ language plpgsql ;

create or replace function fn_scanfolders (int_userid int4, int_scanfolderid int4) returns setof scanfolder as
$function$

DECLARE rec RECORD;
BEGIN
	IF fn_hasrole(int_userid, 'Admin'::varchar) THEN
		IF int_scanfolderid IS NULL THEN
			FOR rec IN 
			SELECT * FROM scanfolder ORDER BY path
			LOOP
				RETURN NEXT rec;
			END LOOP;
		ELSE 
			FOR rec IN 
			SELECT * FROM scanfolder
			WHERE scanfolderid = int_scanfolderid
			LOOP
				RETURN NEXT rec;
			END LOOP;
		END IF;
	END IF;
END;
$function$ language plpgsql ;

create or replace function tf_classification_btrig () returns trigger as
$function$

DECLARE
BEGIN

	-- Update the parentpath
	IF NEW.parentid IS NOT NULL THEN
		NEW.parentpath := (SELECT parentpath 
				FROM classification
				WHERE classificationid = NEW.parentid)
			|| NEW.classificationid::text::ltree; 
	ELSE 
		NEW.parentpath = NEW.classificationid::text::ltree; 
	END IF;
	RETURN NEW;
END;

$function$ language plpgsql ;

create or replace function tf_workflowstep_btrig () returns trigger as
$function$

DECLARE
BEGIN

	-- Update the parentpath
	IF NEW.parentid IS NOT NULL THEN
		NEW.parentpath := (SELECT parentpath 
				FROM workflowstep 
				WHERE workflowstepid = NEW.parentid)
			|| NEW.workflowstepid::text::ltree; 
	ELSE 
		NEW.parentpath = NEW.workflowstepid::text::ltree; 
	END IF;
	RETURN NEW;
END;

$function$ language plpgsql ;

create or replace function fn_changepassword(int_userid int4, var_newpassword varchar) returns int as
$function$

DECLARE

BEGIN
	UPDATE "user" 
	SET password = var_newpassword, lastpwchange = current_timestamp 
	WHERE userid = int_userid;
	
	RETURN 1;
END;

$function$ language plpgsql ;


create or replace function fn_checkpwstrength (varchar, int4) returns int4 as
$function$

# Check proposed password for strength requirements, return an int indicating whether
# it passes or fails. 

set varchar_password $1
set int_strength $2

case $int_strength {
	4 {
		# Upper, lower, special and number
		if {[string length $varchar_password] >= 8 &&
		    [regexp {[A-Z]} $varchar_password] &&
		    [regexp {[a-z]} $varchar_password] &&
		    [regexp {[^A-Za-z0-9]} $varchar_password] &&
		    [regexp {[0-9]} $varchar_password]} {
			return 1
		}
	}
	3 {
		# Upper, Lower, Number
		if {[string length $varchar_password] >= 8 &&
		    [regexp {[A-Z]} $varchar_password] &&
		    [regexp {[a-z]} $varchar_password] &&
		    [regexp {[0-9]} $varchar_password]} {
			return 1
		}
	}
	2 {
		# Upper, Lower
		if {[string length $varchar_password] >= 8 &&
		    [regexp {[A-Z]} $varchar_password] &&
		    [regexp {[a-z]} $varchar_password]} {
			return 1
		}
	}
	1 { 
		# Length
		if {[string length $varchar_password] >= 8} {
			return 1
		}
	}
	0 {
		# None
		return 1
	}
}
return 0

$function$ language pltcl;

create or replace function fn_completeworkflowstep (int_userid int4, int_workflowstepid int4, int_fileid int4) returns int as
$function$
DECLARE
	int_wfstagid int;
	int_filetagid int;
BEGIN
	SELECT INTO int_wfstagid DISTINCT wfstagid 
	FROM fn_workflowpendings(int_userid,
		int_workflowstepid);
	IF NOT FOUND THEN
		RETURN 0;
	END IF;

	INSERT INTO filetag (fileid, tagid, userid)
	VALUES (int_fileid, int_wfstagid, int_userid)
	RETURNING filetagid INTO int_filetagid;

	RETURN int_filetagid; 
END;
$function$
language plpgsql ;

create or replace function fn_workflowpendings (int_userid int4, int_workflowstepid int4) returns setof vw_workflowpending as
$function$
DECLARE
	rec RECORD;
BEGIN
	FOR rec IN SELECT * 
			FROM vw_workflowpending
			WHERE (int_workflowstepid IS NULL 
				OR workflowstepid = int_workflowstepid)
			AND wfsuserid = int_userid
	LOOP
		RETURN NEXT rec;
	END LOOP;
END;
$function$ language plpgsql ;

create or replace function fn_workflows (int_userid int4, int_workflowid int4) returns setof workflow as
$function$
DECLARE
	rec RECORD;
BEGIN
	IF NOT fn_hasrole(int_userid, 'WorkflowAdmin'::varchar) THEN
		RETURN ;
	END IF;

	FOR rec IN SELECT * 
			FROM workflow
			WHERE (int_workflowid IS NULL 
				OR workflowid = int_workflowid)
	LOOP
		RETURN NEXT rec;
	END LOOP;
END;
$function$ language plpgsql ;

create or replace function fn_workflowsteps (int_userid int4, int_workflowid int4, int_workflowstepid int4) returns setof vw_workflow as
$function$

DECLARE
	rec RECORD;
BEGIN

	FOR rec IN SELECT * 
			FROM vw_workflow
			WHERE (workflowid = int_workflowid 
				OR int_workflowid IS NULL)
				AND (int_workflowstepid IS NULL 
				OR workflowstepid = int_workflowstepid)
				AND (fn_hasrole(int_userid, 
					'WorkflowAdmin'::varchar) 
				OR wfsuserid = int_userid)
			ORDER BY parentpath
	LOOP
		RETURN NEXT rec;
	END LOOP;

END;
$function$ language plpgsql ;

create or replace function fn_workflow (int_workflowid int4, int_userid int4, varchar_workflowname varchar, int_filetypeid int4, int_tagid int4) returns integer as 
$function$

DECLARE
	int_newworkflowid integer;
BEGIN
	IF NOT fn_hasrole(int_userid, 'WorkflowAdmin'::varchar) THEN
		RETURN 0;
	END IF;

	IF int_workflowid IS NULL THEN
		-- Insert
		INSERT INTO workflow (userid, workflowname, filetypeid, tagid)
			VALUES (int_userid, varchar_workflowname, int_filetypeid, int_tagid)
			RETURNING workflowid INTO int_newworkflowid;
		RETURN int_newworkflowid;
	ELSE 
		-- Update
		UPDATE workflow 
			SET workflowname = varchar_workflowname,
				filetypeid = int_filetypeid,
				tagid = int_tagid
			WHERE workflowid = int_workflowid;
		RETURN int_workflowid;
	END IF;
END;
$function$ language plpgsql ;

create or replace function fn_addworkflowstep (int_userid int4, int_workflowid int4, int_parentid int4, int_wfuserid int4, int_workflowtypeid int4, interval_revertafter interval) returns integer as 
$function$

DECLARE
	int_workflowstepid integer;
BEGIN
	IF NOT fn_hasrole(int_userid, 'WorkflowAdmin'::varchar) THEN
		RETURN 0;
	END IF;

	BEGIN
		INSERT INTO workflowstep (workflowid, parentid, workflowtypeid, userid,
			revertafter)
		VALUES (int_workflowid, int_parentid, int_workflowtypeid, int_wfuserid,
			interval_revertafter)
		RETURNING workflowid INTO int_workflowstepid;
	EXCEPTION 
		WHEN unique_violation THEN
			RETURN 0;
	END;
	RETURN int_workflowstepid;
END;
$function$ language plpgsql ;

create or replace function fn_delworkflowstep (int_userid int4, int_workflowstepid int4) returns int as
$function$

DECLARE
	int_workflowid int;
BEGIN
	IF NOT fn_hasrole(int_userid, 'WorkflowAdmin'::varchar) THEN
		RETURN 0;
	END IF;
	
	DELETE FROM workflowstep 
		WHERE workflowstepid = int_workflowstepid
		RETURNING workflowid INTO int_workflowid;

	RETURN int_workflowid;

END;

$function$ language plpgsql ;

create or replace function isworkflowtag (int_tagid int4) returns boolean as 
$function$
BEGIN
	RETURN (SELECT workflowtag
			FROM tag 
			WHERE tagid = int_tagid);
END;
$function$ immutable language plpgsql ;

create or replace function tf_filetag_butrig() returns trigger as 
$function$
DECLARE
BEGIN
	IF NEW.untaguserid IS NOT NULL
		AND OLD.untaguserid IS NULL THEN

		NEW.untagdate := current_timestamp;

	END IF;
	RETURN NEW;
END;

$function$ language plpgsql ;

create or replace function fn_deltag (int_userid int4, int_tagid int4, int_mergetagid int4) returns int4 as 
$function$

DECLARE
BEGIN

	-- Delete a tag optionally updating all references to a new tag.
	IF (NOT fn_hasrole(int_userid, 'Admin'::varchar)) OR (int_tagid IS NULL) THEN
		RETURN 0;
	END IF;

	IF int_mergetagid IS NOT NULL THEN

		-- Update references to the new tag

		UPDATE filetag 
		SET tagid = int_mergetagid
		WHERE tagid = int_tagid
		AND untaguserid IS NULL
		AND NOT EXISTS (
			SELECT * 
			FROM filetag ft
			WHERE ft.fileid = filetag.fileid
			AND untaguserid IS NULL
			AND tagid = int_mergetagid);

		UPDATE filetypetag 
		SET tagid = int_mergetagid
		WHERE tagid = int_tagid
		AND NOT EXISTS (
			SELECT *
			FROM filetypetag ftt
			WHERE ftt.filetypeid = filetypetag.filetypeid
			AND tagid = int_mergetagid);
	END IF;

	-- Delete the tag.  ON DELETE CASCADE keys will take care of any 
	-- that survived the above loop, or all of them.

	DELETE FROM tag WHERE tagid = int_tagid;

	RETURN 1;
END;

$function$ language plpgsql ;

create or replace function fn_addfiletypetag (int_userid int4, int_filetypeid int4, varchar_tagname varchar) returns int4 as
$function$

DECLARE
	int_tagid int4;
BEGIN
	-- Add a given tag to a given filetype assuming they have admin access

	IF NOT fn_hasrole(int_userid, 'Admin'::varchar) THEN
		return 0;
	END IF;

	SELECT INTO int_tagid tagid 
		FROM tag 
		WHERE tagname ilike varchar_tagname;

	IF int_tagid IS NULL THEN
		INSERT INTO tag (tagname) 
			VALUES (varchar_tagname)
			RETURNING tagid INTO int_tagid;
	END IF;

	IF NOT EXISTS (SELECT * FROM filetypetag
		WHERE filetypeid = int_filetypeid
		AND tagid = int_tagid) THEN

		INSERT INTO filetypetag (filetypeid, tagid)
		VALUES (int_filetypeid, int_tagid);
	END IF;
	RETURN 1;
END;

$function$ language plpgsql ;

create or replace function fn_delfiletypetag (int_userid int4, int_filetypeid int4, int_filetypetagid int4) returns int4 as
$function$

DECLARE 
BEGIN
	-- Delete filetypetag for a filetype, or a specific filetypetag.
	-- TODO check metadata group membership

	IF int_filetypetagid IS NOT NULL OR int_filetypeid IS NOT NULL THEN
		DELETE FROM filetypetag 
		WHERE (int_filetypeid IS NULL OR filetypeid = int_filetypeid)
		AND (int_filetypetagid IS NULL OR
			filetypetagid = int_filetypetagid);

		RETURN 1;
	END IF;
	RETURN 0;
END;
$function$ language plpgsql ;

create or replace function fn_filetypetags (int_userid int4, int_filetypeid int4, int_filetypetagid int4) returns setof vw_filetypetag as
$function$

DECLARE rec record;
BEGIN
	FOR rec IN SELECT * FROM vw_filetypetag
		WHERE (int_filetypeid IS NULL OR filetypeid = int_filetypeid)
		AND EXISTS (SELECT * FROM fn_filetypes(
				int_userid, 
				int_filetypeid,
				NULL))
		AND (int_filetypetagid IS NULL OR
			filetypetagid = int_filetypetagid)
	LOOP
		RETURN NEXT rec;
	END LOOP;
END
$function$ language plpgsql ;

/* installation wide
create or replace function public.fn_persistsession (var_sessionid varchar, var_sessiondata varchar) returns int4 as 
$function$

DECLARE
BEGIN

	BEGIN
		INSERT INTO session (sessionid, sessiondata)
		VALUES (var_sessionid, var_sessiondata);
	EXCEPTION 
		WHEN unique_violation THEN
		UPDATE session 
		SET sessiondata = var_sessiondata,
		lastupdate = current_timestamp
		WHERE sessionid = var_sessionid;
	END;
	RETURN 1;
END;
$function$ language plpgsql ;
*/

create or replace function fn_filetypes (int_userid int4, int_filetypeid int4, int_classificationid int4) returns setof vw_filetype as
$function$

-- Needs userid filter because not everyone can scan to every file type?
-- Also needs classid filter (optional) to limit to the logged on class.

DECLARE rec RECORD;
BEGIN
	
	FOR rec IN SELECT DISTINCT vf.* 
			FROM vw_filetype vf 
			JOIN vw_userclassification vuc
			ON vf.classificationid = vuc.classificationid
			WHERE (int_filetypeid IS NULL OR filetypeid = int_filetypeid)
			AND (int_classificationid IS NULL 
				OR vf.classificationid = int_classificationid)
			ORDER BY filetypename LOOP
		RETURN NEXT rec;
	END LOOP;
END;

$function$ language plpgsql ;

create or replace function fn_filetype (int_userid int4, int_filetypeid int4, var_filetypename varchar, var_filetypedesc varchar, int_classificationid int4, int_retentionid int4, int_scanoptionid int4, bool_ocr bool) returns int4 as 
$function$

DECLARE 
	int_newfiletypeid int4;
BEGIN
	IF NOT fn_hasrole(int_userid, 'Admin'::varchar) THEN
		RETURN 0;
	END IF;

	IF int_filetypeid IS NULL THEN

		-- New record

		INSERT INTO filetype (filetypename, filetypedesc, 
			classificationid, retentionid, scanoptionid, ocr)
		VALUES (var_filetypename, var_filetypedesc,
			int_classificationid, int_retentionid,
			int_scanoptionid, bool_ocr)
		RETURNING filetypeid INTO int_newfiletypeid;
		RETURN int_newfiletypeid;
	ELSE 
		-- Update record

		UPDATE filetype SET filetypedesc = var_filetypedesc,
			classificationid = int_classificationid,
			retentionid = int_retentionid,
			scanoptionid = int_scanoptionid,
			ocr = bool_ocr
		WHERE filetypeid = int_filetypeid;
		RETURN int_filetypeid;

	END IF;
END;

$function$ language plpgsql ;

create or replace function fn_hasrole (int_userid int4, varchar_role varchar) returns boolean as
$function$

DECLARE
BEGIN
	IF EXISTS (SELECT * FROM vw_userrole
			WHERE userid = int_userid
			AND rolename = varchar_role) THEN
		RETURN true;
	ELSE
		RETURN false;
		RAISE NOTICE '% is not in % role!', int_userid, varchar_role;
	END IF;
END;
$function$ language plpgsql ;

create or replace function fn_users (int_userid int4, int_edituserid int4) returns setof vw_user as
$function$

DECLARE rec RECORD;
BEGIN
	IF fn_hasrole(int_userid, 'Admin'::varchar) THEN
		IF int_edituserid IS NULL THEN
			FOR rec IN 
			SELECT * FROM vw_user ORDER BY username
			LOOP
				RETURN NEXT rec;
			END LOOP;
		ELSE 
			FOR rec IN 
			SELECT * FROM vw_user  
			WHERE userid = int_edituserid
			ORDER BY username
			LOOP
				RETURN NEXT rec;
			END LOOP;
		END IF;
	END IF;
END;

$function$ language plpgsql ;

create or replace function fn_deluserrole (int_userid int4, int_userroleid int4) returns int4 as 
$function$

DECLARE
BEGIN
	IF fn_hasrole(int_userid, 'Admin'::varchar) THEN
		IF EXISTS (SELECT * FROM userrole 
			WHERE userroleid = int_userroleid
			AND revokeddate IS NULL) THEN

			UPDATE userrole 
			SET revokeddate = current_timestamp,
				revokedby = int_userid
			WHERE userroleid = int_userroleid;

			RETURN 1;
		END IF;
	END IF;
	RETURN 0;
END;
$function$ language plpgsql ;

create or replace function fn_adduserrole (int_userid int4, int_edituserid int4, int_roleid int4) returns int4 as 
$function$

DECLARE
BEGIN
	IF fn_hasrole(int_userid, 'Admin'::varchar) THEN
		IF NOT EXISTS (SELECT * FROM userrole 
			WHERE userid = int_edituserid
			AND roleid = int_roleid 
			AND revokeddate IS NULL) THEN

			INSERT INTO userrole (userid, roleid, grantedby)
			VALUES (int_edituserid, int_roleid, int_userid);
		END IF;
		RETURN 1;
	END IF;
	RETURN 0;
END;
$function$ language plpgsql ;

create or replace function fn_changeuserclass (int_userid int4, int_edituserid int4, int_newclassid int4) returns int4 as
$function$

DECLARE
	-- Change a given users max classification.
BEGIN
	IF fn_hasrole(int_userid, 'Admin'::varchar) THEN

		UPDATE userclassification 
		SET revokeddate = current_timestamp,
			revokedby = int_userid
		WHERE userid = int_edituserid
		AND classificationid != int_newclassid
		AND revokeddate IS NULL;

		BEGIN
	
			INSERT INTO userclassification
			(userid, classificationid, grantedby)
			VALUES (int_edituserid, int_newclassid, int_userid);

		EXCEPTION
			WHEN unique_violation THEN
			-- Ignore
		END;

		RETURN 1;
	END IF;	
	RETURN 0;
END;

$function$ language plpgsql ;

create or replace function fn_user (int_userid int4, int_edituserid int4, varchar_username varchar, varchar_password varchar, bool_enabled bool, int_classificationid int4) returns int4 as 
$function$
DECLARE int_newuserid int4;
	-- create or update a user record.
BEGIN
	IF fn_hasrole(int_userid, 'Admin'::varchar) THEN

		IF int_edituserid IS NULL THEN

			-- This is an insert

			INSERT INTO "user" (username, password) 
			VALUES (varchar_username, varchar_password)
			RETURNING userid INTO int_newuserid;

			PERFORM fn_changeuserclass (int_userid, int_newuserid, 
				int_classificationid);

			RETURN int_newuserid;

		ELSE

			-- This is an update

			UPDATE "user" SET enabled = bool_enabled
			WHERE userid = int_edituserid;

			IF varchar_password IS NOT NULL THEN
			
				UPDATE "user" SET password = varchar_password
				WHERE userid = int_edituserid;
			
			END IF;

			PERFORM fn_changeuserclass (int_userid, int_edituserid, 
				int_classificationid);

			RETURN int_edituserid;
		END IF;
	END IF;
	return 0;
END;
$function$ language plpgsql ;

create or replace function fn_addfiletag (int_userid int4, int_fileid int4, varchar_tagname varchar) returns int4 as
$function$

DECLARE int_tagid integer; 
BEGIN
	-- Add a give tag to a given file assuming they have access
	-- If already tagged with this tag, just untag using this userid first

	SELECT fn_checktag(varchar_tagname) INTO int_tagid;

	IF EXISTS (SELECT * FROM vw_fileuser
		WHERE fileid = int_fileid
		AND userid = int_userid) THEN

		UPDATE filetag SET untaguserid = int_userid
		WHERE fileid = int_fileid AND tagid = int_tagid
			AND untaguserid IS NULL;

		INSERT INTO filetag (fileid, tagid, userid)
		VALUES (int_fileid, int_tagid, int_userid);

	END IF;
	RETURN 1;
END;
$function$ language plpgsql ;

create or replace function fn_delfiletag (int_userid int4, int_fileid int4, int_filetagid int4) returns int4 as
$function$

DECLARE 
BEGIN
	-- Delete filetags for a file, or a specific filetag.
	IF int_filetagid IS NOT NULL OR int_fileid IS NOT NULL THEN
		UPDATE filetag 
		SET untaguserid = int_userid
		WHERE (int_fileid IS NULL OR fileid = int_fileid)
		AND EXISTS (SELECT * FROM vw_fileuser
				WHERE fileid = filetag.fileid
				AND userid = int_userid) 
		AND (int_filetagid IS NULL OR
			filetagid = int_filetagid)
		AND untaguserid IS NULL;
	END IF;
	RETURN 1;
END;
$function$ language plpgsql ;

create or replace function fn_filetags (int_userid int4, int_fileid int4, int_filetagid int4) returns setof vw_filetag as
$function$

DECLARE rec record;
BEGIN
	FOR rec IN SELECT * FROM vw_filetag
		WHERE (int_fileid IS NULL OR fileid = int_fileid)
		AND EXISTS (SELECT * FROM vw_fileuser
				WHERE fileid = vw_filetag.fileid
				AND userid = int_userid) 
		AND (int_filetagid IS NULL OR
			filetagid = int_filetagid)
	LOOP
		RETURN NEXT rec;
	END LOOP;
END
$function$ language plpgsql ;

create or replace function fn_insertview (int_userid int4, int_fileid int4) returns int4 as
$function$

DECLARE
BEGIN
	INSERT INTO fileview (userid, fileid)
	VALUES (int_userid, int_fileid);
	RETURN 1;
END

$function$ language plpgsql ;


create or replace function fn_updatefilename (int_userid int4, var_filename varchar, var_newfilename varchar) returns int as
$function$

-- When a file is replaced, the filename gets updated to the backup file name

DECLARE
BEGIN

	UPDATE file SET filename = var_newfilename WHERE filename = var_filename
	AND EXISTS (SELECT fileid FROM vw_fileuser WHERE fileid = file.fileid AND 
		userid = int_userid);
	IF FOUND THEN
    		RETURN 1;
	ELSE 	
		RETURN 0;
	END IF;
END

$function$ language plpgsql ;


create or replace function fn_userroles (int_userid int4, int_userroleid int4) returns setof vw_userrole as
$function$

	DECLARE rec RECORD;

	BEGIN
		IF int_userid IS NOT NULL THEN
			FOR rec IN SELECT * FROM vw_userrole 
			WHERE userid = int_userid LOOP
				RETURN NEXT rec;
			END LOOP;
		ELSIF int_userroleid IS NOT NULL THEN
			FOR rec IN SELECT * FROM vw_userrole 
			WHERE userroleid = int_userroleid 
			AND howgranted = 'EXPLICIT' LOOP
				RETURN NEXT rec;
			END LOOP;
		END IF;
	END;
$function$ language plpgsql ;

create or replace function tf_role_btrig () returns trigger as 
$function$
	DECLARE
	BEGIN
		NEW.parentpath := parentpath || fn_int2ltree(NEW.roleid) 
		FROM "role" WHERE roleid = NEW.parentroleid ;
		RETURN NEW;
	END;
		
$function$ language plpgsql ;

create or replace function tf_crypt_pw () returns trigger as 
$function$

DECLARE
BEGIN

	IF substr(NEW.password,1,3) != '$1$' THEN
		NEW.password := crypt(NEW.password, gen_salt('md5'));
	END IF;

	RETURN NEW;
END;
$function$ language plpgsql ;


create or replace function fn_searchresults (
	int_userid int, 
	arr_tags anyarray, 
	arr_keywords anyarray, 
	arr_filetypes anyarray,
	dt_datefrom date,
	dt_dateto date,
	OUT fileid integer,
	OUT filename varchar,
	OUT headline text,
	OUT rank real)
	RETURNS SETOF record
	language plpgsql as $function$

-- Get files that match all of the tags, keywords and filetypes provided for
-- the search.

DECLARE rec record;
	b boolean;
BEGIN

select arr_keywords is null into b;
	RETURN QUERY
	SELECT DISTINCT ON (file.filename) 
		file.fileid, 
		file.filename, 
		ts_headline(ocrdata,q), 
		ts_rank(idxfti,q)
	FROM file
	JOIN vw_fileuser fu 
		ON file.fileid = fu.fileid
		AND fu.userid = int_userid 
	,to_tsquery(array_to_string(arr_keywords,'&')) q
	WHERE	(arr_tags IS NULL 
		OR (
			SELECT COUNT(*) FROM vw_filetag 
			WHERE vw_filetag.fileid = file.fileid 
				AND tagname = ANY(arr_tags)) = ARRAY_UPPER(arr_tags,1))
		AND 
		(arr_keywords IS NULL 
		OR idxfti @@ q)
		AND
		(arr_filetypes IS NULL
		OR (
			SELECT COUNT(*) FROM file f join filetype ft
			ON f.filetypeid = ft.filetypeid
			WHERE f.fileid = file.fileid
				AND filetypename = ANY(arr_filetypes)) = ARRAY_UPPER(arr_filetypes,1))
		AND
		(dt_datefrom IS NULL
		OR file.filedate >= dt_datefrom)
		AND 
		(dt_dateto IS NULL
		OR file.filedate <= dt_dateto)
		ORDER BY file.filename, COALESCE(file.fileid, file.parentfileid) DESC;

END;

$function$ ;

create or replace function fn_file (int_userid int4, int_fileid int) returns setof vw_file language plpgsql as $function$

-- Get file attributes given a file id if the user is allowed to see them from vw_file

DECLARE rec vw_file;
BEGIN
	SELECT INTO rec vf.* FROM vw_file vf JOIN vw_fileuser fu
	ON vf.fileid = fu.fileid
	WHERE fu.userid = int_userid
	AND fu.fileid = int_fileid;
	RETURN NEXT rec;
END;
$function$ ;

create or replace function fn_browsedates (int_userid int4, int_year int4, int_month int) returns setof int4 language plpgsql as $function$

-- Get list of date components for a date that have files that are viewable by the user
-- given date constraint

DECLARE 
	rec INTEGER;
	cur_tmp REFCURSOR;

BEGIN

	IF int_month IS NOT NULL AND int_year IS NOT NULL THEN
		OPEN cur_tmp FOR
		SELECT DISTINCT date_part('day',filedate) as day FROM file f 
		JOIN vw_fileuser uf ON f.fileid = uf.fileid 
		AND uf.userid = int_userid WHERE 
		date_part('year',filedate) = int_year 
		AND date_part('month',filedate) = int_month
		ORDER BY date_part('day',filedate);
	ELSIF int_year IS NOT NULL THEN
		OPEN cur_tmp FOR
		SELECT DISTINCT date_part('month',filedate) as month FROM file f 
		JOIN vw_fileuser uf ON f.fileid = uf.fileid 
		AND uf.userid = int_userid WHERE 
		date_part('year',filedate) = int_year 
		ORDER BY date_part('month',filedate);
	ELSE
		OPEN cur_tmp FOR
		SELECT DISTINCT date_part('year',filedate) as year FROM file f 
		JOIN vw_fileuser uf ON f.fileid = uf.fileid 
		AND uf.userid = int_userid 
		ORDER BY date_part('year',filedate);
	END IF;
	FETCH cur_tmp INTO rec;
	WHILE FOUND LOOP
		RETURN NEXT rec;
		FETCH cur_tmp INTO rec;
	END LOOP;
	RETURN;
END;

$function$ ;

create or replace function fn_scanoptions (var_filetypename varchar) returns setof scanoption language plpgsql as $function$

DECLARE rec scanoption;
	cur_tmp REFCURSOR;

BEGIN
	IF var_filetypename != '' THEN
		OPEN cur_tmp FOR
                SELECT * FROM scanoption WHERE scanoptionid = 
                        -- It might be new, so we coalesce
                        COALESCE(
                        (SELECT scanoptionid FROM filetype 
                        WHERE filetypename = var_filetypename), 
                        (SELECT scanoptionid FROM scanoption WHERE 
                        scanoptionname = 'Default')); 
        ELSE
		OPEN cur_tmp FOR
                SELECT * FROM scanoption WHERE scanoptionid = 
                        -- or it might be missing.
                        (SELECT scanoptionid FROM scanoption WHERE 
                        scanoptionname = 'Default'); 
	END IF;
	FETCH cur_tmp INTO rec;
	WHILE FOUND LOOP
		RETURN NEXT rec;
		FETCH cur_tmp INTO rec;
	END LOOP;
	RETURN;
END;

$function$ ;


--drop type browselist cascade;
create type browselist as (fileid int4, filename varchar);

create or replace function fn_browsefiles (int_userid int4, var_how varchar, int_year int4, int_month int4, int_day int4, var_filetypename varchar, var_tagname varchar) returns setof browselist language plpgsql as $function$

-- Get list of fileid and filename that meet the criteria and are viewable by the user
-- given date or filetype constraint

DECLARE 
	rec RECORD;

BEGIN

	IF var_how = 'date' THEN
		FOR rec IN 
		SELECT DISTINCT ON (f.filename) f.fileid, f.filename FROM file f 
		JOIN vw_fileuser uf ON f.fileid = uf.fileid 
		AND uf.userid = int_userid 
		LEFT OUTER JOIN file fp ON f.fileid = fp.parentfileid
		WHERE fp.fileid IS NULL 
		AND
		date_part('year',f.filedate) = int_year 
		AND (int_month IS NULL OR 
			date_part('month',f.filedate) = int_month)
		AND (int_day IS NULL OR 
			date_part('day',f.filedate) = int_day) 
		ORDER BY f.filename DESC
		LOOP
			RETURN NEXT rec;
		END LOOP;
		
	ELSIF var_how = 'type' THEN
		FOR rec IN 
		SELECT DISTINCT ON (f.filename) f.fileid, f.filename FROM file f 
		JOIN vw_fileuser uf ON f.fileid = uf.fileid 
		AND uf.userid = int_userid 
		LEFT OUTER JOIN file fp ON f.fileid = fp.parentfileid
		WHERE fp.fileid IS NULL 
		AND
		f.filetypeid = (SELECT filetypeid FROM filetype 
			WHERE filetypename = var_filetypename) 
		ORDER BY f.filename DESC
		LOOP
			RETURN NEXT rec;
		END LOOP;
	ELSIF var_how = 'tag' THEN
		FOR rec IN 
		SELECT DISTINCT ON (f.filename) f.fileid, f.filename FROM file f 
		JOIN vw_fileuser uf ON f.fileid = uf.fileid 
		AND uf.userid = int_userid 
		JOIN vw_filetag ft ON f.fileid = ft.fileid
		JOIN tag t ON ft.tagid = t.tagid
		LEFT OUTER JOIN file fp ON f.fileid = fp.parentfileid
		WHERE fp.fileid IS NULL 
		AND
		t.tagname = var_tagname
		ORDER BY f.filename DESC
		LOOP
			RETURN NEXT rec;
		END LOOP;
	END IF;
	RETURN;
END;

$function$ ;


create or replace function fn_checkscanoption (int, int4, varchar, boolean) returns int4 language pltcl as 
$function$

# Check for existence of a scanoption record with the exact same values
# or create one from scratch.

set brightness $1
set contrast $2
set mode $3
set duplex $4

if {![info exists GD(planSelect)]} {

	set GD(planSelect) [ spi_prepare  \
		"SELECT scanoptionid FROM scanoption WHERE 
		 brightness = \$1 AND contrast = \$2
		 and mode = \$3 and duplex = \$4" \
		[list int4 int4 varchar boolean] ]
}
spi_execp -count 1 $GD(planSelect) [list $brightness $contrast $mode $duplex]

if {![info exists scanoptionid]} {

	if {![info exists GD(planInsert)]} {
		set GD(planInsert) [ spi_prepare \
		"INSERT INTO scanoption (scanoptionname, brightness, 
		contrast, mode, duplex) VALUES (
		\$1 || ' ' || CASE WHEN \$2 THEN 'duplex' ELSE 'simplex' END || 
		' brightness:' || 
		to_char(\$3,'FM999') || ' contrast:' ||
		to_char(\$4,'FM999'), \$3, \$4, \$1, \$2)
		RETURNING scanoptionid" \
		[list varchar boolean int4 int4] ]
	} 
	spi_execp $GD(planInsert) [list $mode $duplex $brightness $contrast] 

}
return $scanoptionid

$function$ ;

create or replace function fn_int2ltree (int) returns ltree language pltcl as 
$function$

return $1

$function$ ;

create or replace function fn_checklogin (varchar, varchar, varchar) returns varchar language pltcl as $function$

# Check login credentials and log in loginattempt.  Return status.

set username $1
set password $2
set classification $3


if {![info exists GD(planLog)]} {

	set GD(planLog) [ spi_prepare \
		"INSERT INTO loginattempt (username, status, classification) VALUES (\$1, \$2, \$3)" \
		[list varchar varchar varchar] ]
}

# see if username is locked out or expired

if {![info exists GD(planExpired)]} {
	set GD(planExpired) [ spi_prepare \
		"SELECT count(*) as expired FROM vw_expireduser WHERE username = \$1" \
		"varchar" ]
}

spi_execp -count 1 $GD(planExpired) $username

if {$expired} {
	
	spi_execp $GD(planLog) [list $username EXPIRED $classification]
	return "EXPIRED"
}

if {![info exists GD(planLockedOut)]} {

	set GD(planLockedOut) [ spi_prepare \
		"SELECT count(*) as lockedout FROM vw_lockout WHERE username = \$1" \
		"varchar" ]
}

spi_execp -count 1 $GD(planLockedOut) $username

if {$lockedout} {
	spi_execp $GD(planLog) [list $username LOCKOUT $classification]
	return "LOCKOUT"
}

# see if the username is good.

if {![info exists GD(planUsername)]} {

	set GD(planUsername) [ spi_prepare \
		"SELECT userid, enabled, lastpwchange FROM \"user\" WHERE username = \$1" \
		"varchar" ]
}
spi_execp -count 1 $GD(planUsername) $username

if {![info exists enabled]} {
	spi_execp $GD(planLog) [list $username NO_SUCH_USER $classification]
	return "NO_SUCH_USER"
}

if {[string match "f" $enabled]} {
	spi_execp $GD(planLog) [list $username DISABLED $classification]
	return "DISABLED"
}
	
# see if the user has that classification

if {![info exists GD(planUserClass)]} {

	set GD(planUserClass) [ spi_prepare \
		"SELECT classificationid, count(*) as classok 
		 FROM vw_userclassification WHERE userid = \$1 
			AND classification = \$2
		 GROUP BY classificationid" \
		"int4 varchar" ]
}
spi_execp -count 1 $GD(planUserClass) [list $userid $classification]

if {![info exists classok] || !$classok} {
	spi_execp $GD(planLog) [list $username BADCLASS $classification]
	return "BADCLASS"
}

if {![info exists GD(planSuccess)]} {

	set GD(planSuccess) [ spi_prepare \
		"SELECT count(*) as success FROM \"user\" WHERE username = \$1
			AND password = crypt(\$2,password)" \
		"varchar varchar" ]
}
spi_execp -count 1 $GD(planSuccess) [list $username $password]

if {$success} {
	spi_execp $GD(planLog) [list $username SUCCESS $classification]
	set sql "SELECT (current_date - pwage) > '$lastpwchange'::date as needpwchange "
	append sql "FROM config"
	elog NOTICE "$sql"
	spi_exec "$sql"
	
	return [list SUCCESS $userid $needpwchange $classificationid]
} else {
	spi_execp $GD(planLog) [list $username FAILED $classification]
	return "FAILED"
}

$function$ ;

create or replace function fn_checkfiletype (varchar, int) returns int4 language pltcl as
$function$

set filetypename $1
set scanoptionid $2

if {![info exists GD(planSelect)]} {

	set GD(planSelect) [ spi_prepare \
		"SELECT filetypeid FROM filetype WHERE filetypename ilike \$1 " \
		"varchar" ]
}
spi_execp $GD(planSelect) $filetypename

if {![info exists filetypeid]} {

	if {![info exists GD(planInsert)]} {
		set GD(planInsert) [ spi_prepare \
			"INSERT INTO filetype (filetypename, scanoptionid) VALUES (upper(\$1), \$2)" \
			[list varchar int4] ]
	}
	spi_execp $GD(planInsert) [list $filetypename $scanoptionid]
	spi_execp $GD(planSelect) $filetypename
}

return $filetypeid
$function$ ;

create or replace function fn_checktag (varchar) returns int4 language pltcl as
$function$

set tagname $1
if {![info exists GD(planSelect)]} {

	set GD(planSelect) [ spi_prepare \
		"SELECT tagid FROM tag WHERE tagname = \$1" \
		"varchar" ]
}
spi_execp $GD(planSelect) $tagname

if {![info exists tagid]} {

	if {![info exists GD(planInsert)]} {
		set GD(planInsert) [spi_prepare \
			"INSERT INTO tag (tagname) VALUES (\$1) RETURNING tagid" \
			"varchar" ]
	}
	spi_execp $GD(planInsert) $tagname
}

return $tagid
$function$ ;

create or replace function fn_insertfile (
	int,		-- userid
	varchar, 	--filename 
	date,  		--filedate
	int, 		--filetypeid
	varchar,	--tag1
	varchar,	--tag...
	varchar,
	varchar,
	varchar,
	boolean,	--update
	varchar, 	--parentfilename
	varchar,	--subpath
	varchar)	--source
	returns int4 language pltcl as 

$function$

# Inserts or updates a file record.

set varlist {userid filename filedate filetypeid tag1 tag2 tag3 tag4 tag5 update parentfile subpath source}
set i 1
foreach var $varlist {
	set $var [set $i]
	elog NOTICE "$var is [set $var]"
	incr i
}

# if update is true, get the old record first.

if {[string match $update "t"]} {
	if {![info exists GD(planSelect)]} {
		set GD(planSelect) [ spi_prepare \
		"SELECT max(fileid) as parentfileid 
		 FROM file WHERE filename = \$1" \
		"varchar" ]
	}
	spi_execp $GD(planSelect) $parentfile
	if {![info exists parentfileid]} {
		# Show stopper if update is true...
		elog ERROR "no parentfile id found"
	}
}

if {![info exists parentfileid]} {
	# This is OK since update is false
	set parentfileid ""
}

# Do last minute check to see if you have that classification 
# authorized

if {![info exists GD(planHaveClass)]} {
	set GD(planHaveClass) [ spi_prepare \
	"SELECT count(*) as haveclass
	 FROM vw_userclassification vu 
	 JOIN filetype ft ON vu.classificationid = ft.classificationid
	 WHERE userid = \$1 AND filetypeid = \$2" \
	[list int4 int4] ]
}
spi_execp $GD(planHaveClass) [list $userid $filetypeid] 

if {!$haveclass} {
	elog ERROR "User not authorized that classification."
}

# Insert into the file table.

if {![info exists GD(planInsertFile)]} {
	set GD(planInsertFile) [ spi_prepare \
		"INSERT INTO file (userid, filepath, filename, filetypeid, filedate,
		parentfileid, subpath, filesource) 
		VALUES (\$1, (SELECT rootpath FROM config), \$2, \$3, \$4, \$5, \$6, \$7)
		RETURNING fileid" \
		[list int4 varchar int4 date int4 varchar varchar] ]
}

if {$update == "t"} {
	set nulls "       "
} else {
	set nulls "    n  "
}

spi_execp -nulls $nulls $GD(planInsertFile) \
	[list $userid $filename $filetypeid $filedate $parentfileid $subpath $source]

# If a filetype was provided, get tags from there and insert into filetag table.

if {![info exists GD(planInsertFileTags)]} {
	set GD(planInsertFileTags) [ spi_prepare \
		"INSERT INTO filetag (fileid, tagid, userid) 
		 SELECT \$1, tagid, \$3 FROM filetypetag 
		 WHERE filetypeid = \$2" \
		[list int4 int4 int4] ]
}
spi_execp $GD(planInsertFileTags) \
	[list $fileid $filetypeid $userid]

# If tags were sent, insert them.  Use checktags and catch unique key errors.

if {![info exists GD(planInsertFileTag)]} {
	set GD(planInsertFileTag) [ spi_prepare \
		"SELECT fn_addfiletag(\$1, \$2, \$3)" \
		[list int4 int4 varchar] ]
}

for {set i 1} {$i <= 5} {incr i} {

	if {[string length [set tag$i]] > 0} {
		spi_execp $GD(planInsertFileTag) \
		[list $userid $fileid [set tag$i]]
	}
}

# Finally, if this is a new filetype, insert the filetypetag records.

set sql "SELECT count(*) AS cnt FROM file WHERE filetypeid = $filetypeid"
spi_exec $sql

if {$cnt == 1} {
	set sql "INSERT INTO filetypetag (filetypeid, tagid) "
	append sql "SELECT f.filetypeid, ft.tagid FROM vw_filetag ft "
	append sql "JOIN file f on ft.fileid = f.fileid "
	append sql "WHERE f.filetypeid = $filetypeid "
	append sql "AND NOT EXISTS (SELECT * FROM filetypetag WHERE "
	append sql "filetypeid = f.filetypeid AND tagid = ft.tagid)"
	spi_exec $sql
}

return $fileid
$function$ ;


